Imports Microsoft.VisualBasic
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.Data
Imports DataAccesGenerales
Imports DataAcces

Public Class DataAccesEmpresas
  Private Const consTimeOut As Integer = 0 
  
  Public Function Empresas_Insertar(pnid As Integer, pcodigo As String, pnombre As String, pRFC As String, pdireccion As String, pciudad As String, pCP As String, prepresentante As String, pidentificacion As String, penviaMail As Integer, pemail As String, ppais As Integer, ppresentacion As Integer, pestado As Integer, ptipo_Expo As Integer, ptipo_Impo As Integer, ppatente As String, precinto As String, psid As Integer, psip_Naviera As Integer, psip_Maniobrista As Integer, pcontatoProveedorServicios As Integer, pvigenciaContratoProveedor As Integer, psip_ControlAccesos As Integer, ptipoFirma As Integer, psip_Proveedor As Integer, pfVigenciaFirmaAPI As Date) As Byte
    Dim pTransaccion As Byte = 0
    Dim mensaje As String = String.Empty
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Empresas_Insertar" 
          oraCommandDocumentos.Parameters.Add("pnid", pnid)
          oraCommandDocumentos.Parameters.Add("pcodigo", pcodigo)
          oraCommandDocumentos.Parameters.Add("pnombre", pnombre)
          oraCommandDocumentos.Parameters.Add("pRFC", pRFC)
          oraCommandDocumentos.Parameters.Add("pdireccion", pdireccion)
          oraCommandDocumentos.Parameters.Add("pciudad", pciudad)
          oraCommandDocumentos.Parameters.Add("pCP", pCP)
          oraCommandDocumentos.Parameters.Add("prepresentante", prepresentante)
          oraCommandDocumentos.Parameters.Add("pidentificacion", pidentificacion)
          oraCommandDocumentos.Parameters.Add("penviaMail", penviaMail)
          oraCommandDocumentos.Parameters.Add("pemail", pemail)
          oraCommandDocumentos.Parameters.Add("ppais", ppais)
          oraCommandDocumentos.Parameters.Add("ppresentacion", ppresentacion)
          oraCommandDocumentos.Parameters.Add("pestado", pestado)
          oraCommandDocumentos.Parameters.Add("ptipo_Expo", ptipo_Expo)
          oraCommandDocumentos.Parameters.Add("ptipo_Impo", ptipo_Impo)
          oraCommandDocumentos.Parameters.Add("ppatente", ppatente)
          oraCommandDocumentos.Parameters.Add("precinto", precinto)
          oraCommandDocumentos.Parameters.Add("psid", psid)
          oraCommandDocumentos.Parameters.Add("psip_Naviera", psip_Naviera)
          oraCommandDocumentos.Parameters.Add("psip_Maniobrista", psip_Maniobrista)
          oraCommandDocumentos.Parameters.Add("pcontatoProveedorServicios", pcontatoProveedorServicios)
          oraCommandDocumentos.Parameters.Add("pvigenciaContratoProveedor", pvigenciaContratoProveedor)
          oraCommandDocumentos.Parameters.Add("psip_ControlAccesos", psip_ControlAccesos)
          oraCommandDocumentos.Parameters.Add("ptipoFirma", ptipoFirma)
          oraCommandDocumentos.Parameters.Add("psip_Proveedor", psip_Proveedor)
          oraCommandDocumentos.Parameters.Add("pfVigenciaFirmaAPI", pfVigenciaFirmaAPI)

          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add("pMensaje", Oracle.DataAccess.Client.OracleDbType.Varchar2, 200).Direction = ParameterDirection.Output

          oraConnection.Open()
          oraCommandDocumentos.ExecuteNonQuery()
          pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
          mensaje = oraCommandDocumentos.Parameters("pMensaje").Value.ToString()
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return pTransaccion
  End Function

  Public Function Empresas_Modificar(pnid As Integer, pcodigo As String, pnombre As String, pRFC As String, pdireccion As String, pciudad As String, pCP As String, prepresentante As String, pidentificacion As String, penviaMail As Integer, pemail As String, ppais As Integer, ppresentacion As Integer, pestado As Integer, ptipo_Expo As Integer, ptipo_Impo As Integer, ppatente As String, precinto As String, psid As Integer, psip_Naviera As Integer, psip_Maniobrista As Integer, pcontatoProveedorServicios As Integer, pvigenciaContratoProveedor As Integer, psip_ControlAccesos As Integer, ptipoFirma As Integer, psip_Proveedor As Integer, pfVigenciaFirmaAPI As Date) As Byte
    Dim pTransaccion As Byte = 0
    Dim mensaje As String = String.Empty
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Empresas_Modificar"
          oraCommandDocumentos.Parameters.Add("pnid", pnid)
          oraCommandDocumentos.Parameters.Add("pcodigo", pcodigo)
          oraCommandDocumentos.Parameters.Add("pnombre", pnombre)
          oraCommandDocumentos.Parameters.Add("pRFC", pRFC)
          oraCommandDocumentos.Parameters.Add("pdireccion", pdireccion)
          oraCommandDocumentos.Parameters.Add("pciudad", pciudad)
          oraCommandDocumentos.Parameters.Add("pCP", pCP)
          oraCommandDocumentos.Parameters.Add("prepresentante", prepresentante)
          oraCommandDocumentos.Parameters.Add("pidentificacion", pidentificacion)
          oraCommandDocumentos.Parameters.Add("penviaMail", penviaMail)
          oraCommandDocumentos.Parameters.Add("pemail", pemail)
          oraCommandDocumentos.Parameters.Add("ppais", ppais)
          oraCommandDocumentos.Parameters.Add("ppresentacion", ppresentacion)
          oraCommandDocumentos.Parameters.Add("pestado", pestado)
          oraCommandDocumentos.Parameters.Add("ptipo_Expo", ptipo_Expo)
          oraCommandDocumentos.Parameters.Add("ptipo_Impo", ptipo_Impo)
          oraCommandDocumentos.Parameters.Add("ppatente", ppatente)
          oraCommandDocumentos.Parameters.Add("precinto", precinto)
          oraCommandDocumentos.Parameters.Add("psid", psid)
          oraCommandDocumentos.Parameters.Add("psip_Naviera", psip_Naviera)
          oraCommandDocumentos.Parameters.Add("psip_Maniobrista", psip_Maniobrista)
          oraCommandDocumentos.Parameters.Add("pcontatoProveedorServicios", pcontatoProveedorServicios)
          oraCommandDocumentos.Parameters.Add("pvigenciaContratoProveedor", pvigenciaContratoProveedor)
          oraCommandDocumentos.Parameters.Add("psip_ControlAccesos", psip_ControlAccesos)
          oraCommandDocumentos.Parameters.Add("ptipoFirma", ptipoFirma)
          oraCommandDocumentos.Parameters.Add("psip_Proveedor", psip_Proveedor)
          oraCommandDocumentos.Parameters.Add("pfVigenciaFirmaAPI", pfVigenciaFirmaAPI)

          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add("pMensaje", Oracle.DataAccess.Client.OracleDbType.Varchar2, 200).Direction = ParameterDirection.Output

          oraConnection.Open()
          oraCommandDocumentos.ExecuteNonQuery()
          pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
          mensaje = oraCommandDocumentos.Parameters("pMensaje").Value.ToString()
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return pTransaccion
  End Function
  
  Function Empresas_Consultar(nid as Integer) As DataTable
    Dim dtResultado As New DataTable("EmpresasConsulta")
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Empresas_Consultar" 
          oraCommandDocumentos.Parameters.Add("pnid", nid)
          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

          Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
          oraDataAdapter.Fill(dtResultado)
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return dtResultado
  End Function
  
  Public Function Empresas_CadenaOriginalXMLChilkat(nidDocumento As Integer) As Chilkat.Xml
    Dim detalleDataSet As New DataSet("DatosCaseta")
    Try
      detalleDataSet = Empresas_DetalleXML(nidDocumento)
	  'En esta parte van las tablas obtenidas en la consulta anterior
	  'separadas dependiendo del reporte a crear, ejemplo:
	  detalleDataSet.Tables(0).TableName = "Informacion del Tramite"
      detalleDataSet.Tables(1).TableName = "Datos Empresas"
      detalleDataSet.Tables(2).TableName = "Archivos Anexos"
      detalleDataSet.Tables(3).TableName = "Incidencias"

      Dim xml As New Chilkat.Xml
      xml.LoadXml(detalleDataSet.GetXml())
      Return xml 'detalleDataSet.GetXml()
    Catch ex As Exception
      Throw ex
    End Try
  End Function

  Public Function Empresas_DetalleXML(ByVal nidDocumento As Integer) As DataSet
    Dim dsDetalle As New DataSet("EmpresasDetalle")
    Dim pTransaccion As Byte = 0
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Empresas_DetalleXML"
          oraCommandDocumentos.Parameters.Add("pNidDocumento", nidDocumento)
          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          'En esta parte es para los cursores necesario 
		  oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pEncabezado", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pDatos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pAnexos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pIncidencias", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
		
          Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
          pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
          oraDataAdapter.Fill(dsDetalle)
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return dsDetalle
  End Function
  
End Class
